from datetime import datetime
from collections import defaultdict
from sqlalchemy import func, select, exc, and_, text
from app.config.db import DBUtils
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist
from app.model.actualcontrollerModel import ActualController


# 图谱数据结构处理
def process_graph(result_list, comb):
    group_fkje_nd = defaultdict(int)
    label_set = set()
    # rel_set = set()
    total_amount = 0
    combo_label = {}
    for row in result_list:
        key_nd = (row['wldw'], row['id'], row['kzid'])
        group_fkje_nd[key_nd] += row['total']
        total_amount += row['total']
        l_kzid = row['kzid']
        l_kzr = row['kzr']
        combo_label[l_kzid] = l_kzr
        # key_nd = (row['kzr'], row['kzid'])
        # if key_nd not in label_set:
        #     label_set.add(key_nd)
        # key_rel = (row['kzid'], row['id'])
        # if key_rel not in rel_set:
        #     rel_set.add(key_rel)
    wldw_nd = [{
        'id': str(id),
        'label': wldw,
        'nodeType': 'wldw',
        'amount': str(round(total / 10000, 2)) if total >= 50 or total == 0 else '0.01',
        'combo': 'k' + str(kzid)
    } for (wldw, id, kzid), total in group_fkje_nd.items()]

    # sjkzr_nd = [{
    #     'id': str(kzid),
    #     'label': kzr,
    #     'nodeType': 'sjkzr'
    # } for kzr, kzid in label_set]
    # combos = [{
    #     'id': 'k' + str(kzid),
    #     'label': kzr,
    #     'combo': comb
    # } for kzr, kzid in label_set]
    combos = [{
        'id': 'k' + str(l_kzid),
        'label': l_kzr,
        'combo': comb
    } for l_kzid, l_kzr in combo_label.items()]

    return wldw_nd, combos , total_amount

class ControllerFlowService:
    def __init__(self):
        self.db_utils = DBUtils()
        # self.current_year = datetime.now().year

    # 需考虑内部交易情况
    def select_controller_flow_info(self, session, company, is_cydw, year):
        combos = [
            {'id': 'cb7', 'label': '上游实控人'},
            {'id': 'cb8', 'label': '下游实控人'}
        ]
        company_label = 'node11'
        # {'id': 'node10', 'label': '上游供应商', 'nodeType': 'wldw', 'combo': 'cb7'},
        # {'id': 'node12', 'label': '下游客户', 'nodeType': 'wldw', 'combo': 'cb8'}
        company_node = [{'id': company_label, 'label': company, 'nodeType': 'cydw'}]
        sk_edge = [{'source': 'cb8', 'target': 'node11', 'elx': 'SK'}]
        fk_edge = [{'source': 'node11', 'target': 'cb7', 'elx': 'FK'}]
        if int(is_cydw) == 1:
            fk_param = "fkzhmc = :param"
            sk_param = "skzhmc = :param"
        elif int(is_cydw) == 2:
            fk_param = "fkgs = :param"
            sk_param = "skgs = :param"
        elif int(is_cydw) == 3:
            fk_param = "1 = 1"
            sk_param = "1 = 1"
        # if start_date and end_date:
        #     condition_fk = fklist.SCRQ.between(start_date, end_date)
        #     condition_sk = sklist.SCRQ.between(start_date, end_date)
        # else:
        #     condition_fk = fklist.JYND == int(self.current_year)
        #     condition_sk = sklist.JYND == int(self.current_year)
        try:
            # upstream_query = (
            #     select(ActualController.NAME,
            #            func.sum(fklist.FKJE).label('total'))
            #     .join(ActualController, fklist.SKZHMC == ActualController.GSMC)
            #     .where(and_(fklist.FKZHMC == company,
            #                 condition_fk, fk_dif_name))
            #     .group_by(ActualController.NAME)
            # )
            # upstream_list = session.execute(upstream_query).fetchall()

            # if start_date and end_date:   #替换为年度
            #     upstream_query = text(f"""
            #         select distinct name as kzr,skzhmc as wldw,gsid as id,kzid,total from (
            #         select name,skzhmc,gsid,kzid,
            #         count(distinct skzhmc) over (PARTITION BY name) as cnt,
            #         sum(fkje) over (PARTITION BY name,skzhmc,gsid) as total
            #         from ESUN.V_BI_GS_KZR_FKLIST
            #         where fkzhmc = :param
            #         and scrq >= :s_date
            #         and scrq <= :e_date
            #         and is_cydw != 1
            #         and js_ywfl != '同名户结算')
            #         where cnt >= 2
            #     """)
            #     downstream_query = text(f"""
            #         select distinct name as kzr,fkzhmc as wldw,gsid as id,kzid,total from (
            #         select name,fkzhmc,gsid,kzid,
            #         count(distinct fkzhmc) over (PARTITION BY name) as cnt,
            #         sum(fkje) over (PARTITION BY name,fkzhmc,gsid) as total
            #         from ESUN.V_BI_GS_KZR_FKLIST
            #         where skzhmc = :param
            #         and scrq >= :s_date
            #         and scrq <= :e_date
            #         and is_cydw != 1
            #         and js_ywfl != '同名户结算')
            #         where cnt >= 2
            #     """)
            #     params = {"param": company, "s_date": start_date, "e_date": end_date}
            #     upstream_list = session.execute(upstream_query, params).mappings()
            #     downstream_list = session.execute(downstream_query, params).mappings()
            # else:
            upstream_query = text(f"""
                select distinct name as kzr,skzhmc as wldw,gsid as id,kzid,total from (
                select name,skzhmc,gsid,kzid,
                count(distinct skzhmc) over (PARTITION BY name) as cnt,
                sum(fkje) over (PARTITION BY name,skzhmc,gsid) as total
                from ESUN.V_BI_GS_KZR_FKLIST
                where {fk_param}
                and is_cydw not in (1,2,3)
                and js_ywfl != '同名户结算'
                and jynd = :in_year
                )
                where cnt >= 2
                ORDER BY total DESC  -- 按总金额降序排序
                FETCH FIRST 5 ROWS ONLY;                      
            """)
            downstream_query = text(f"""
                select distinct name as kzr,fkzhmc as wldw,gsid as id,kzid,total from (
                select name,fkzhmc,gsid,kzid,
                count(distinct fkzhmc) over (PARTITION BY name) as cnt,
                sum(fkje) over (PARTITION BY name,fkzhmc,gsid) as total
                from ESUN.V_BI_GS_KZR_FKLIST
                where {sk_param}
                and is_cydw not in (1,2,3)
                and js_ywfl != '同名户结算'
                and jynd = :in_year
                )
                where cnt >= 2       
                ORDER BY total DESC  -- 按总金额降序排序
                FETCH FIRST 5 ROWS ONLY;                           
            """)
            upstream_list = session.execute(upstream_query,
                                            {'param': company, 'in_year': year}).mappings()
            downstream_list = session.execute(downstream_query,
                                              {'param': company, 'in_year': year}).mappings()
            # downstream_query = (
            #     select(ActualController.NAME,
            #            func.sum(sklist.FKJE).label('total'))
            #     .join(ActualController, sklist.FKZHMC == ActualController.GSMC)
            #     .where(and_(sklist.SKZHMC == company,
            #                 condition_sk, sk_dif_name))
            #     .group_by(ActualController.NAME)
            # )
            # downstream_list = session.execute(downstream_query).fetchall()

            if upstream_list:
                upstream_nodes, upstream_combs, up_total = process_graph(upstream_list, 'cb7')
                fk_edge[0]['label'] = str(round(up_total / 10000, 2)) if up_total >= 50 or up_total == 0 else '0.01'
            else:
                upstream_nodes = []
                upstream_combs = []
                fk_edge = [{'source': 'node11', 'target': 'cb7', 'elx': 'FK', 'label': 0}]

            if downstream_list:
                downstream_nodes, downstream_combs, down_total = process_graph(downstream_list, 'cb8')
                sk_edge[0]['label'] = str(round(down_total / 10000, 2)) if down_total >= 50 or down_total == 0 else '0.01'
            else:
                downstream_nodes = []
                downstream_combs = []
                sk_edge = [{'source': 'cb8', 'target': 'node11', 'elx': 'SK', 'label': 0}]

            nodes = upstream_nodes + downstream_nodes + company_node
            # print(upstream_edges, downstream_edges, sk_edge, fk_edge)
            edges = sk_edge + fk_edge
            combs = combos + upstream_combs + downstream_combs
            result = [{
                'nodes': nodes,
                'edges': edges,
                'combos': combs,
            }]
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
                self.db_utils.remove_session()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

        return result


        #     if upstream_list:
        #         up_id = 1
        #         upstream_nodes = []
        #         upstream_edges = []
        #         for row in upstream_list:
        #             strid = 'nd' + str(up_id)
        #             comid = 'com' + str(up_id)
        #             if row.total >= 50:
        #                 str_total = str(round(row.total / 10000, 2))
        #             else:
        #                 str_total = '0.01'
        #             upstream_node = {
        #                 'id': strid,
        #                 'label': row.NAME,
        #                 'nodeType': 'sjkzr',
        #                 'amount': str_total,
        #                 'combo': 'cb7'}
        #             upstream_nodes.append(upstream_node)
        #             {id: 'combo3', label: '一院', combo: "combo1"},
        #             upstream_combo = {
        #                 'id': comid,
        #                 'label'
        #             }
        #             # upstream_edge = {
        #             #     'source': strid,
        #             #     'target': 'node10',
        #             #     'label': str_total,
        #             #     'elx': 'sjkzr'}
        #             # upstream_edges.append(upstream_edge)
        #             up_id += 1
        #
        #         up_total = sum(float(row['label']) for row in upstream_edges)
        #         fk_edge[0]['label'] = str(round(up_total / 10000, 2)) if up_total >= 50 else '0.01'
        #     else:
        #         upstream_nodes = []
        #         upstream_edges = []
        #         fk_edge = []
        #
        #     if downstream_list:
        #         down_id = 1000
        #         downstream_nodes = []
        #         downstream_edges = []
        #         for row in downstream_list:
        #             strid = 'nd' + str(down_id)
        #             if row.total >= 50:
        #                 str_total = str(round(row.total / 10000, 2))
        #             else:
        #                 str_total = '0.01'
        #             downstream_node = {
        #                 'id': strid,
        #                 'label': row.NAME,
        #                 'nodeType': 'sjkzr',
        #                 'amount': str_total,
        #                 'combo': 'cb8'}
        #             downstream_nodes.append(downstream_node)
        #             # downstream_edge = {
        #             #     'source': strid,
        #             #     'target': 'node12',
        #             #     'label': str_total,
        #             #     'elx': 'sjkzr'}
        #             # downstream_edges.append(downstream_edge)
        #             down_id += 1
        #
        #         down_total = sum(float(row['label']) for row in downstream_edges)
        #         sk_edge[0]['label'] = str(round(down_total / 10000, 2)) if down_total >= 50 else '0.01'
        #     else:
        #         downstream_nodes = []
        #         downstream_edges = []
        #         sk_edge = []
        #
        #     nodes = upstream_nodes + downstream_nodes + company_node
        #     # print(upstream_edges, downstream_edges, sk_edge, fk_edge)
        #     edges = upstream_edges + downstream_edges + sk_edge + fk_edge
        #     result = [{
        #         'nodes': nodes,
        #         'edges': edges,
        #         'combos': combos,
        #     }]
        # except exc.SQLAlchemyError as e:
        #     error_response = [{'error': str(e)}]
        #     try:
        #         self.db_utils.rollback()
        #     except Exception as rollback_error:
        #         print(f"Failed to rollback transaction: {rollback_error}")
        #     return error_response
        # finally:
        #     try:
        #         self.db_utils.remove_session()
        #     except Exception as remove_error:
        #         print(f"Failed to rollback transaction: {remove_error}")
        #
        # return result
